package userManage.Model.service;

import userManage.Model.dao.connect;
import userManage.Model.entity.record;
import userManage.Model.entity.user;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;

public class serviceImpl {
    //查看全体成员信息
    public static ArrayList<user> searchNumber() throws SQLException {
        ArrayList<user> users = new ArrayList<>();
        Connection conn = connect.getConnection();
        String sql = "select * from user";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            user a = new user();
            a.setUsername(rs.getString("username"));
            a.setPassword(rs.getString("password"));
            a.setPostbox(rs.getString("postbox"));
            a.setPhoneNumber(rs.getString("phoneNumber"));
            a.setName(rs.getString("name"));
            a.setDirection(rs.getString("direction"));
            a.setPhase(rs.getString("phase"));
            users.add(a);
        }
        rs.close();
        pstmt.close();
        return users;
    }

    //注销账户
    public static int deleteNumber(String userName) throws SQLException {
        Connection conn = connect.getConnection();
        // sql 查询语句
        String sql = "delete from user where username=?;";
        // 获得执行sql语句的对象
        PreparedStatement pstatement = conn.prepareStatement(sql);
        pstatement.setString(1, userName);
        int count = pstatement.executeUpdate();
        pstatement.close();
        return count;
    }

    //查看图书借阅记录
    public static ArrayList<record> searchDate(String userName) throws SQLException {
        ArrayList<record> records = new ArrayList<>();
        Connection conn = connect.getConnection();
        String sql = "select * from record where username=?;";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, userName);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            record br = new record();
            br.setUsername(rs.getString("username"));
            br.setBookName(rs.getString("bookName"));
            br.setNumber(rs.getString("number"));
            br.setBorrowTime(rs.getDate("borrowTime"));
            br.setReturnTime(rs.getDate("returnTime"));
            br.setBookId(rs.getInt("bookId"));
            records.add(br);
        }
        rs.close();
        pstmt.close();
        return records;
    }

    public static int agreeBorrow(String userName, String bookName, String number, int bookId) throws Exception {
        Connection conn = connect.getConnection();
        String sql = "INSERT INTO record (username, bookName, number, borrowTime, bookId) VALUE (?,?,?,NOW(),?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, userName);
        pstmt.setString(2, bookName);
        pstmt.setString(3, number);
        pstmt.setInt(4, bookId);
        int count = pstmt.executeUpdate();
        pstmt.close();
        toolBorrow(bookId, userName);
        return count;
    }

    public static int agreeReturnBook(String userName, String bookName, int bookId) throws SQLException {
        Connection conn = connect.getConnection();
        String sql = "update record set returnTime=NOW() where username=? and bookName=? and bookId=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, userName);
        pstmt.setString(2, bookName);
        pstmt.setInt(3, bookId);
        int count = pstmt.executeUpdate();
        pstmt.close();
        toolReturn(bookName, bookId);
        return count;
    }

    //工具类
    public static void toolBorrow(int bookId , String userName) throws SQLException {
        Connection conn = connect.getConnection();
        String sql = "update book set status = '借出', borrower = ?, borrowNumber = borrowNumber + 1 where id= ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, userName);
        pstmt.setInt(2, bookId);
        int count = pstmt.executeUpdate();
        pstmt.close();
    }

    //工具类
    public static void toolReturn(String bookName, int bookId) throws SQLException {
        Connection conn = connect.getConnection();
        String sql = "update book set status = '在馆' , borrower = null where bookName= ? and id =?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, bookName);
        pstmt.setInt(2, bookId);
        int count = pstmt.executeUpdate();
        pstmt.close();
    }
}
